/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package javafxapplication1;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import org.apache.poi.hssf.record.DVRecord;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddressList;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.DataValidation;

/**
 *
 * @author my
 */
public class ExcelReader {
    
    HSSFSheet sheet;
    
    public List<ColumnInfo> read(File xlsFile) throws FileNotFoundException, IOException{
        List<ColumnInfo> cols=new ArrayList<>();

        final POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(xlsFile));
        final HSSFWorkbook bk = new HSSFWorkbook(poifsFileSystem);
        sheet = bk.getSheetAt(0);
        final HSSFRow row = sheet.getRow(1);
        HSSFRow dataRow=sheet.getRow(1);
      
        ColumnInfo columnInfo ;
        for(int i=0;i<row.getPhysicalNumberOfCells();i++){
            columnInfo = new ColumnInfo();
            final HSSFCell cell = row.getCell(i);
            final HSSFRichTextString richStringCellValue = cell.getRichStringCellValue();
         
            if(dataRow!=null ){
               
                HSSFCell cell1 = dataRow.getCell(i);
                if(cell1!=null){
                    CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
                    String[] cellRefParts = cellRef.getCellRefParts();
                    System.out.println(Arrays.toString(cellRefParts));
                    HSSFComment cellComment = cell1.getCellComment();
                    if(cellComment!=null)
                    System.out.println("cellFormula="+cellComment.getWrapText());
                  
                    
                    
                }
            }
            columnInfo.setName(richStringCellValue.getString());
            final HSSFComment cellComment = cell.getCellComment();
            if(cellComment!=null){
                columnInfo.setComments(cellComment.getString().getString());
            }
            cols.add(columnInfo);
        }

        return cols;
    }

    public String getRange(){
        
        return null;
    }
    
    
    public HSSFSheet getSheet() {
        return sheet;
    }

    public void setSheet(HSSFSheet sheet) {
        this.sheet = sheet;
    }
        
        
}
